ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
class.ilTestTopList.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2015 ILIAS open source, Extended GPL, see docs/LICENSE */
3
8{
10 protected $object;
11
15 public function __construct(ilObjTest $a_object)
16 {
17 $this->object = $a_object;
18 }
19
25 public function getUserToplistByWorkingtime($a_test_ref_id, $a_user_id)
26 {
28 global $DIC;
29 $ilDB = $DIC['ilDB'];
30
31 // Get placement of user
32 $result = $ilDB->query(
33 '
34 SELECT count(tst_pass_result.workingtime) as count
35 FROM object_reference
36 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
37 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
38 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
39 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
40 AND tst_pass_result.pass = tst_result_cache.pass
41 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
42 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
43 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
44 AND workingtime <
45 (
46 SELECT workingtime
47 FROM object_reference
48 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
49 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
50 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
51 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
52 AND tst_pass_result.pass = tst_result_cache.pass
53 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
54 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
55 )
56 '
57 );
58
59 $row = $ilDB->fetchAssoc($result);
60 $better_participants = $row['count'];
61 $own_placement = $better_participants + 1;
62
63 $result = $ilDB->query(
64 '
65 SELECT count(tst_pass_result.workingtime) as count
66 FROM object_reference
67 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
68 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
69 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
70 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
71 AND tst_pass_result.pass = tst_result_cache.pass
72 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
73 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer')
74 );
75 $row = $ilDB->fetchAssoc($result);
76 $number_total = $row['count'];
77
78 $result = $ilDB->query(
79 '
80 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage ,
81 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
82 FROM object_reference
83 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
84 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
85 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
86 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
87 AND tst_pass_result.pass = tst_result_cache.pass
88 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
89
90 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
91 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
92
93 UNION(
94 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage,
95 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
96 FROM object_reference
97 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
98 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
99 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
100 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
101 AND tst_pass_result.pass = tst_result_cache.pass
102 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
103 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
104 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
105 AND workingtime >=
106 (
107 SELECT tst_pass_result.workingtime
108 FROM object_reference
109 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
110 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
111 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
112 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
113 AND tst_pass_result.pass = tst_result_cache.pass
114 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
115 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
116 )
117 ORDER BY workingtime DESC
118 LIMIT 0,3
119 )
120 UNION(
121 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage,
122 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
123 FROM object_reference
124 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
125 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
126 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
127 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
128 AND tst_pass_result.pass = tst_result_cache.pass
129 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
130 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
131 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
132 AND workingtime <
133 (
134 SELECT tst_pass_result.workingtime
135 FROM object_reference
136 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
137 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
138 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
139 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
140 AND tst_pass_result.pass = tst_result_cache.pass
141 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
142 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
143 )
144 ORDER BY workingtime DESC
145 LIMIT 0,3
146 )
147 ORDER BY workingtime ASC
148 LIMIT 0, 7
149 '
150 );
151
152 $i = $own_placement - (($better_participants >= 3) ? 3 : $better_participants);
153
154 $data = array();
155
156 if ($i > 1) {
157 $item = array('Rank' => '...');
158 $data[] = $item;
159 }
160
162 while ($row = $ilDB->fetchAssoc($result)) {
163 $item = $this->getResultTableRow($row, $i, $a_user_id);
164 $i++;
165 $data[] = $item;
166 }
167
168 if ($number_total > $i) {
169 $item = array('Rank' => '...');
170 $data[] = $item;
171 }
172
173 return $data;
174 }
175
181 public function getGeneralToplistByPercentage($a_test_ref_id, $a_user_id)
182 {
184 global $DIC;
185 $ilDB = $DIC['ilDB'];
186 $result = $ilDB->query(
187 '
188 SELECT tst_result_cache.*, round(points/maxpoints*100,2) as percentage, tst_pass_result.workingtime, usr_data.usr_id, usr_data.firstname, usr_data.lastname
189 FROM object_reference
190 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
191 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
192 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
193 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi AND tst_pass_result.pass = tst_result_cache.pass
194 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
195 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
196 ORDER BY percentage DESC
197 LIMIT 0, ' . $ilDB->quote($this->object->getHighscoreTopNum(), 'integer') . '
198 '
199 );
200 $i = 0;
201 $data = array();
203 while ($row = $ilDB->fetchAssoc($result)) {
204 $i++;
205 $item = $this->getResultTableRow($row, $i, $a_user_id);
206
207 $data[] = $item;
208 }
209 return $data;
210 }
211
217 public function getGeneralToplistByWorkingtime($a_test_ref_id, $a_user_id)
218 {
220 global $DIC;
221 $ilDB = $DIC['ilDB'];
222 $result = $ilDB->query(
223 '
224 SELECT tst_result_cache.*, round(points/maxpoints*100,2) as percentage, tst_pass_result.workingtime, usr_data.usr_id, usr_data.firstname, usr_data.lastname
225 FROM object_reference
226 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
227 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
228 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
229 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi AND tst_pass_result.pass = tst_result_cache.pass
230 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
231 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
232 ORDER BY workingtime ASC
233 LIMIT 0, ' . $ilDB->quote($this->object->getHighscoreTopNum(), 'integer') . '
234 '
235 );
236 $i = 0;
237 $data = array();
239 while ($row = $ilDB->fetchAssoc($result)) {
240 $i++;
241 $item = $this->getResultTableRow($row, $i, $a_user_id);
242 $data[] = $item;
243 }
244 return $data;
245 }
246
252 public function getUserToplistByPercentage($a_test_ref_id, $a_user_id)
253 {
255 global $DIC;
256 $ilDB = $DIC['ilDB'];
257
258 // Get placement of user
259 $result = $ilDB->query(
260 '
261 SELECT count(tst_pass_result.workingtime) as count
262 FROM object_reference
263 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
264 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
265 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
266 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
267 AND tst_pass_result.pass = tst_result_cache.pass
268 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
269 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
270 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
271 AND round(reached_points/max_points*100) >=
272 (
273 SELECT round(reached_points/max_points*100)
274 FROM object_reference
275 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
276 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
277 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
278 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
279 AND tst_pass_result.pass = tst_result_cache.pass
280 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
281 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
282 )
283 '
284 );
285
286 $row = $ilDB->fetchAssoc($result);
287 $better_participants = $row['count'];
288 $own_placement = $better_participants + 1;
289
290 $result = $ilDB->query(
291 '
292 SELECT count(tst_pass_result.workingtime) as count
293 FROM object_reference
294 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
295 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
296 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
297 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
298 AND tst_pass_result.pass = tst_result_cache.pass
299 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
300 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer')
301 );
302 $row = $ilDB->fetchAssoc($result);
303 $number_total = $row['count'];
304
305 $result = $ilDB->query(
306 '
307 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage ,
308 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
309 FROM object_reference
310 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
311 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
312 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
313 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
314 AND tst_pass_result.pass = tst_result_cache.pass
315 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
316
317 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
318 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
319
320 UNION(
321 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage,
322 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
323 FROM object_reference
324 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
325 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
326 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
327 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
328 AND tst_pass_result.pass = tst_result_cache.pass
329 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
330 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
331 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
332 AND round(reached_points/max_points*100) >=
333 (
334 SELECT round(reached_points/max_points*100)
335 FROM object_reference
336 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
337 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
338 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
339 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
340 AND tst_pass_result.pass = tst_result_cache.pass
341 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
342 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
343 )
344 ORDER BY round(reached_points/max_points*100) ASC
345 LIMIT 0,3
346 )
347 UNION(
348 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage,
349 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
350 FROM object_reference
351 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
352 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
353 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
354 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
355 AND tst_pass_result.pass = tst_result_cache.pass
356 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
357 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
358 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
359 AND round(reached_points/max_points*100) <=
360 (
361 SELECT round(reached_points/max_points*100)
362 FROM object_reference
363 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
364 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
365 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
366 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
367 AND tst_pass_result.pass = tst_result_cache.pass
368 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
369 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
370 )
371 ORDER BY round(reached_points/max_points*100) ASC
372 LIMIT 0,3
373 )
374 ORDER BY round(reached_points/max_points*100) DESC, tstamp ASC
375 LIMIT 0, 7
376 '
377 );
378
379 $i = $own_placement - (($better_participants >= 3) ? 3 : $better_participants);
380
381 $data = array();
382
383 if ($i > 1) {
384 $item = array('Rank' => '...');
385 $data[] = $item;
386 }
387
389 while ($row = $ilDB->fetchAssoc($result)) {
390 $item = $this->getResultTableRow($row, $i, $a_user_id);
391 $i++;
392 $data[] = $item;
393 }
394
395 if ($number_total > $i) {
396 $item = array('Rank' => '...');
397 $data[] = $item;
398 }
399
400 return $data;
401 }
402
409 private function getResultTableRow($row, $i, $a_user_id)
410 {
411 $item = array();
412 $item['Rank'] = $i . '. ';
413
414 if ($this->object->isHighscoreAnon() && $row['usr_id'] != $a_user_id) {
415 $item['Participant'] = "-, -";
416 } else {
417 $item['Participant'] = $row['lastname'] . ', ' . $row['firstname'];
418 }
419
420 if ($this->object->getHighscoreAchievedTS()) {
421 $item['Achieved'] = new ilDateTime($row['tstamp'], IL_CAL_UNIX);
422 }
423
424 if ($this->object->getHighscoreScore()) {
425 $item['Score'] = $row['reached_points'] . ' / ' . $row['max_points'];
426 }
427
428 if ($this->object->getHighscorePercentage()) {
429 $item['Percentage'] = $row['percentage'] . '%';
430 }
431
432 if ($this->object->getHighscoreHints()) {
433 $item['Hints'] = $row['hint_count'];
434 }
435
436 if ($this->object->getHighscoreWTime()) {
437 $item['time'] = $this->formatTime($row['workingtime']);
438 }
439
440 $item['Highlight'] = ($row['usr_id'] == $a_user_id) ? 'tblrowmarked' : '';
441 return $item;
442 }
443
448 private function formatTime($seconds)
449 {
450 $retval = '';
451 $hours = intval(intval($seconds) / 3600);
452 $retval .= str_pad($hours, 2, "0", STR_PAD_LEFT) . ":";
453 $minutes = intval(($seconds / 60) % 60);
454 $retval .= str_pad($minutes, 2, "0", STR_PAD_LEFT) . ":";
455 $seconds = intval($seconds % 60);
456 $retval .= str_pad($seconds, 2, "0", STR_PAD_LEFT);
457 return $retval;
458 }
459}
$result
An exception for terminatinating execution or to throw for unit testing.
const IL_CAL_UNIX
@classDescription Date and time handling
Class ilTestTopList.
getResultTableRow($row, $i, $a_user_id)
__construct(ilObjTest $a_object)
$i
Definition: disco.tpl.php:19
$row
global $DIC
Definition: saml.php:7
global $ilDB
$data
Definition: bench.php:6