ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
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 $i = $own_placement - (($better_participants >= 3) ? 3 : $better_participants);
151
152 $data = array();
153
154 if($i > 1)
155 {
156 $item = array('Rank' => '...');
157 $data[] = $item;
158 }
159
161 while($row = $ilDB->fetchAssoc($result))
162 {
163
164 $item = $this->getResultTableRow($row, $i, $a_user_id);
165 $i++;
166 $data[] = $item;
167 }
168
169 if($number_total > $i)
170 {
171 $item = array('Rank' => '...');
172 $data[] = $item;
173 }
174
175 return $data;
176
177 }
178
184 public function getGeneralToplistByPercentage($a_test_ref_id, $a_user_id)
185 {
187 global $ilDB;
188 $result = $ilDB->query(
189 '
190 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
191 FROM object_reference
192 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
193 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
194 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
195 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi AND tst_pass_result.pass = tst_result_cache.pass
196 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
197 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
198 ORDER BY percentage DESC
199 LIMIT 0, ' . $ilDB->quote($this->object->getHighscoreTopNum(), 'integer') . '
200 '
201 );
202 $i = 0;
203 $data = array();
205 while($row = $ilDB->fetchAssoc($result))
206 {
207 $i++;
208 $item = $this->getResultTableRow($row, $i, $a_user_id);
209
210 $data[] = $item;
211 }
212 return $data;
213 }
214
220 public function getGeneralToplistByWorkingtime($a_test_ref_id, $a_user_id)
221 {
223 global $ilDB;
224 $result = $ilDB->query(
225 '
226 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
227 FROM object_reference
228 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
229 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
230 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
231 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi AND tst_pass_result.pass = tst_result_cache.pass
232 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
233 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
234 ORDER BY workingtime ASC
235 LIMIT 0, ' . $ilDB->quote($this->object->getHighscoreTopNum(), 'integer') . '
236 '
237 );
238 $i = 0;
239 $data = array();
241 while($row = $ilDB->fetchAssoc($result))
242 {
243 $i++;
244 $item = $this->getResultTableRow($row, $i, $a_user_id);
245 $data[] = $item;
246 }
247 return $data;
248 }
249
255 public function getUserToplistByPercentage($a_test_ref_id, $a_user_id)
256 {
258 global $ilDB;
259
260 // Get placement of user
261 $result = $ilDB->query(
262 '
263 SELECT count(tst_pass_result.workingtime) as count
264 FROM object_reference
265 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
266 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
267 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
268 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
269 AND tst_pass_result.pass = tst_result_cache.pass
270 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
271 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
272 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
273 AND round(reached_points/max_points*100) >=
274 (
275 SELECT round(reached_points/max_points*100)
276 FROM object_reference
277 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
278 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
279 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
280 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
281 AND tst_pass_result.pass = tst_result_cache.pass
282 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
283 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
284 )
285 '
286 );
287
288 $row = $ilDB->fetchAssoc($result);
289 $better_participants = $row['count'];
290 $own_placement = $better_participants + 1;
291
292 $result = $ilDB->query(
293 '
294 SELECT count(tst_pass_result.workingtime) as count
295 FROM object_reference
296 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
297 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
298 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
299 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
300 AND tst_pass_result.pass = tst_result_cache.pass
301 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
302 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer')
303 );
304 $row = $ilDB->fetchAssoc($result);
305 $number_total = $row['count'];
306
307 $result = $ilDB->query(
308 '
309 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage ,
310 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
311 FROM object_reference
312 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
313 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
314 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
315 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
316 AND tst_pass_result.pass = tst_result_cache.pass
317 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
318
319 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
320 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
321
322 UNION(
323 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage,
324 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
325 FROM object_reference
326 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
327 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
328 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
329 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
330 AND tst_pass_result.pass = tst_result_cache.pass
331 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
332 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
333 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
334 AND round(reached_points/max_points*100) >=
335 (
336 SELECT round(reached_points/max_points*100)
337 FROM object_reference
338 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
339 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
340 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
341 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
342 AND tst_pass_result.pass = tst_result_cache.pass
343 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
344 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
345 )
346 ORDER BY round(reached_points/max_points*100) ASC
347 LIMIT 0,3
348 )
349 UNION(
350 SELECT tst_result_cache.*, round(reached_points/max_points*100) as percentage,
351 tst_pass_result.workingtime, usr_id, usr_data.firstname, usr_data.lastname
352 FROM object_reference
353 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
354 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
355 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
356 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
357 AND tst_pass_result.pass = tst_result_cache.pass
358 INNER JOIN usr_data ON usr_data.usr_id = tst_active.user_fi
359 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
360 AND tst_active.user_fi != ' . $ilDB->quote($a_user_id, 'integer') . '
361 AND round(reached_points/max_points*100) <=
362 (
363 SELECT round(reached_points/max_points*100)
364 FROM object_reference
365 INNER JOIN tst_tests ON object_reference.obj_id = tst_tests.obj_fi
366 INNER JOIN tst_active ON tst_tests.test_id = tst_active.test_fi
367 INNER JOIN tst_result_cache ON tst_active.active_id = tst_result_cache.active_fi
368 INNER JOIN tst_pass_result ON tst_active.active_id = tst_pass_result.active_fi
369 AND tst_pass_result.pass = tst_result_cache.pass
370 WHERE object_reference.ref_id = ' . $ilDB->quote($a_test_ref_id, 'integer') . '
371 AND tst_active.user_fi = ' . $ilDB->quote($a_user_id, 'integer') . '
372 )
373 ORDER BY round(reached_points/max_points*100) ASC
374 LIMIT 0,3
375 )
376 ORDER BY round(reached_points/max_points*100) DESC, tstamp ASC
377 LIMIT 0, 7
378 ');
379
380 $i = $own_placement - (($better_participants >= 3) ? 3 : $better_participants);
381
382 $data = array();
383
384 if($i > 1)
385 {
386 $item = array('Rank' => '...');
387 $data[] = $item;
388 }
389
391 while($row = $ilDB->fetchAssoc($result))
392 {
393
394 $item = $this->getResultTableRow($row, $i, $a_user_id);
395 $i++;
396 $data[] = $item;
397 }
398
399 if($number_total > $i)
400 {
401 $item = array('Rank' => '...');
402 $data[] = $item;
403 }
404
405 return $data;
406 }
407
414 private function getResultTableRow($row, $i, $a_user_id)
415 {
416 $item = array();
417 $item['Rank'] = $i . '. ';
418
419 if($this->object->isHighscoreAnon() && $row['usr_id'] != $a_user_id)
420 {
421 $item['Participant'] = "-, -";
422 }
423 else
424 {
425 $item['Participant'] = $row['lastname'] . ', ' . $row['firstname'];
426 }
427
428 if($this->object->getHighscoreAchievedTS())
429 {
430 $item['Achieved'] = new ilDateTime($row['tstamp'], IL_CAL_UNIX);
431
432 }
433
434 if($this->object->getHighscoreScore())
435 {
436 $item['Score'] = $row['reached_points'] . ' / ' . $row['max_points'];
437 }
438
439 if($this->object->getHighscorePercentage())
440 {
441 $item['Percentage'] = $row['percentage'] . '%';
442 }
443
444 if($this->object->getHighscoreHints())
445 {
446 $item['Hints'] = $row['hint_count'];
447 }
448
449 if($this->object->getHighscoreWTime())
450 {
451 $item['time'] = $this->formatTime($row['workingtime']);
452 }
453
454 $item['Highlight'] = ($row['usr_id'] == $a_user_id) ? 'tblrowmarked' : '';
455 return $item;
456 }
457
462 private function formatTime($seconds)
463 {
464 $retval = '';
465 $hours = intval(intval($seconds) / 3600);
466 $retval .= str_pad($hours, 2, "0", STR_PAD_LEFT) . ":";
467 $minutes = intval(($seconds / 60) % 60);
468 $retval .= str_pad($minutes, 2, "0", STR_PAD_LEFT) . ":";
469 $seconds = intval($seconds % 60);
470 $retval .= str_pad($seconds, 2, "0", STR_PAD_LEFT);
471 return $retval;
472 }
473}
$result
const IL_CAL_UNIX
@classDescription Date and time handling
Class ilTestTopList.
getResultTableRow($row, $i, $a_user_id)
__construct(ilObjTest $a_object)
$data
global $ilDB