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