ILIAS  release_6 Revision v6.24-5-g0c8bfefb3b8
class.ilLOUserResults.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
3
12{
13 protected $course_obj_id; // [int]
14 protected $user_id; // [int]
15
16 const TYPE_INITIAL = 1;
17 const TYPE_QUALIFIED = 2;
18
20 const STATUS_FAILED = 2;
21
29 public function __construct($a_course_obj_id, $a_user_id)
30 {
31 $this->course_obj_id = (int) $a_course_obj_id;
32 $this->user_id = (int) $a_user_id;
33 }
34
35
39 public static function lookupResult($a_course_obj_id, $a_user_id, $a_objective_id, $a_tst_type)
40 {
41 global $DIC;
42
43 $ilDB = $DIC['ilDB'];
44
45 $query = 'SELECT * FROM loc_user_results ' .
46 'WHERE user_id = ' . $ilDB->quote($a_user_id, 'integer') . ' ' .
47 'AND course_id = ' . $ilDB->quote($a_course_obj_id, 'integer') . ' ' .
48 'AND objective_id = ' . $ilDB->quote($a_objective_id, 'integer') . ' ' .
49 'AND type = ' . $ilDB->quote($a_tst_type, 'integer');
50 $res = $ilDB->query($query);
51 $ur = array(
52 'status' => self::STATUS_FAILED,
53 'result_perc' => 0,
54 'limit_perc' => 0,
55 'tries' => 0,
56 'is_final' => 0,
57 'has_result' => false
58 );
59 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
60 $ur['status'] = $row->status;
61 $ur['result_perc'] = $row->result_perc;
62 $ur['limit_perc'] = $row->limit_perc;
63 $ur['tries'] = $row->tries;
64 $ur['is_final'] = $row->is_final;
65 $ur['has_result'] = true;
66 }
67 return $ur;
68 }
69
70 public static function resetFinalByObjective($a_objective_id)
71 {
72 $query = 'UPDATE loc_user_results ' .
73 'SET is_final = ' . $GLOBALS['DIC']['ilDB']->quote(0, 'integer') . ' ' .
74 'WHERE objective_id = ' . $GLOBALS['DIC']['ilDB']->quote($a_objective_id, 'integer');
75 $GLOBALS['DIC']['ilDB']->manipulate($query);
76 }
77
78
85 protected static function isValidType($a_type)
86 {
87 return in_array((int) $a_type, array(self::TYPE_INITIAL, self::TYPE_QUALIFIED));
88 }
89
96 protected static function isValidStatus($a_status)
97 {
98 return in_array((int) $a_status, array(self::STATUS_COMPLETED, self::STATUS_FAILED));
99 }
100
107 public static function deleteResultsForUser($a_user_id)
108 {
109 global $DIC;
110
111 $ilDB = $DIC['ilDB'];
112
113 if (!(int) $a_user_id) {
114 return false;
115 }
116
117 $ilDB->manipulate("DELETE FROM loc_user_results" .
118 " WHERE user_id = " . $ilDB->quote($a_user_id, "integer"));
119 return true;
120 }
121
122
129 public static function deleteResultsForCourse($a_course_id)
130 {
131 global $DIC;
132
133 $ilDB = $DIC['ilDB'];
134
135 if (!(int) $a_course_id) {
136 return false;
137 }
138
139 $ilDB->manipulate("DELETE FROM loc_user_results" .
140 " WHERE course_id = " . $ilDB->quote($a_course_id, "integer"));
141 return true;
142 }
143
148 public function delete()
149 {
150 global $DIC;
151
152 $ilDB = $DIC['ilDB'];
153
154 $query = 'DELETE FROM loc_user_results ' .
155 'WHERE course_id = ' . $ilDB->quote($this->course_obj_id) . ' ' .
156 'AND user_id = ' . $ilDB->quote($this->user_id);
157 $ilDB->manipulate($query);
158 }
159
170 public static function deleteResultsFromLP($a_course_id, array $a_user_ids, $a_remove_initial, $a_remove_qualified, array $a_objective_ids)
171 {
172 global $DIC;
173
174 $ilDB = $DIC['ilDB'];
175
176 if (!(int) $a_course_id ||
177 !sizeof($a_user_ids)) {
178 return false;
179 }
180
181 $base_sql = "DELETE FROM loc_user_results" .
182 " WHERE course_id = " . $ilDB->quote($a_course_id, "integer") .
183 " AND " . $ilDB->in("user_id", $a_user_ids, "", "integer");
184
185 if ((bool) $a_remove_initial) {
186 $sql = $base_sql .
187 " AND type = " . $ilDB->quote(self::TYPE_INITIAL, "integer");
188 $ilDB->manipulate($sql);
189 }
190
191 if ((bool) $a_remove_qualified) {
192 $sql = $base_sql .
193 " AND type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
194 $ilDB->manipulate($sql);
195 }
196
197 if (is_array($a_objective_ids)) {
198 $sql = $base_sql .
199 " AND " . $ilDB->in("objective_id", $a_objective_ids, "", "integer");
200 $ilDB->manipulate($sql);
201 }
202
203 $ilDB->manipulate($sql);
204 return true;
205 }
206
207
220 public function saveObjectiveResult($a_objective_id, $a_type, $a_status, $a_result_percentage, $a_limit_percentage, $a_tries, $a_is_final)
221 {
222 global $DIC;
223
224 $ilDB = $DIC['ilDB'];
225
226 if (!self::isValidType($a_type) ||
227 !self::isValidStatus($a_status)) {
228 return false;
229 }
230 $ilDB->replace(
231 "loc_user_results",
232 array(
233 "course_id" => array("integer", $this->course_obj_id),
234 "user_id" => array("integer", $this->user_id),
235 "objective_id" => array("integer", $a_objective_id),
236 "type" => array("integer", $a_type)
237 ),
238 array(
239 "status" => array("integer", $a_status),
240 "result_perc" => array("integer", $a_result_percentage),
241 "limit_perc" => array("integer", $a_limit_percentage),
242 "tries" => array("integer", $a_tries),
243 "is_final" => array("integer", $a_is_final),
244 "tstamp" => array("integer", time()),
245 )
246 );
247 return true;
248 }
249
258 protected function findObjectiveIds($a_type = null, $a_status = null, $a_is_final = null)
259 {
260 global $DIC;
261
262 $ilDB = $DIC['ilDB'];
263
264 $res = array();
265
266 $sql = "SELECT objective_id" .
267 " FROM loc_user_results" .
268 " WHERE course_id = " . $ilDB->quote($this->course_obj_id, "integer") .
269 " AND user_id = " . $ilDB->quote($this->user_id, "integer");
270
271 if ($this->isValidType($a_type)) {
272 $sql .= " AND type = " . $ilDB->quote($a_type, "integer");
273 }
274 if ($this->isValidStatus($a_status)) {
275 $sql .= " AND status = " . $ilDB->quote($a_status, "integer");
276 }
277 if ($a_is_final !== null) {
278 $sql .= " AND is_final = " . $ilDB->quote($a_is_final, "integer");
279 }
280
281 $set = $ilDB->query($sql);
282 while ($row = $ilDB->fetchAssoc($set)) {
283 $res[] = $row["objective_id"];
284 }
285
286 return $res;
287 }
288
295 {
296 return $this->findObjectiveIds($a_type, self::STATUS_COMPLETED);
297 }
298
304 public function getSuggestedObjectiveIds()
305 {
306 return $this->findObjectiveIds(self::TYPE_INITIAL, self::STATUS_FAILED);
307 }
308
314 public function getCompletedObjectiveIds()
315 {
316 include_once './Modules/Course/classes/Objectives/class.ilLOSettings.php';
317 $settings = ilLOSettings::getInstanceByObjId($this->course_obj_id);
318
319 if (!$settings->isInitialTestQualifying() or !$settings->worksWithInitialTest()) {
320 return $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_COMPLETED);
321 }
322
323 // status of final final test overwrites initial qualified.
324 if (
325 $settings->isInitialTestQualifying() &&
326 $settings->worksWithInitialTest()
327 ) {
328 $completed = array();
329 $completed_candidates = array_unique(
330 array_merge(
331 $this->findObjectiveIds(self::TYPE_INITIAL, self::STATUS_COMPLETED),
332 $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_COMPLETED)
333 )
334 );
335 $failed_final = (array) $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_FAILED);
336
337 foreach ($completed_candidates as $objective_completed) {
338 if (!in_array($objective_completed, $failed_final)) {
339 $completed[] = $objective_completed;
340 }
341 }
342 return $completed;
343 }
344 }
345
352 public function getFailedObjectiveIds($a_is_final = true)
353 {
354 return $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_FAILED, $a_is_final);
355 }
356
363 {
364 global $DIC;
365
366 $ilDB = $DIC['ilDB'];
367
368 $res = array();
369
370 include_once("./Modules/Course/classes/Objectives/class.ilLOSettings.php");
371 $settings = ilLOSettings::getInstanceByObjId($this->course_obj_id);
372
373 $set = $ilDB->query("SELECT *" .
374 " FROM loc_user_results" .
375 " WHERE course_id = " . $ilDB->quote($this->course_obj_id, "integer") .
376 " AND user_id = " . $ilDB->quote($this->user_id, "integer"));
377 while ($row = $ilDB->fetchAssoc($set)) {
378 // do not read initial test results, if disabled.
379 if (
380 $row['type'] == self::TYPE_INITIAL &&
381 !$settings->worksWithInitialTest()
382 ) {
383 continue;
384 }
385
386 $objective_id = $row["objective_id"];
387 $type = $row["type"];
388 unset($row["objective_id"]);
389 unset($row["type"]);
390 $res[$objective_id][$type] = $row;
391 }
392
393 return $res;
394 }
395
396 public static function getObjectiveStatusForLP($a_user_id, $a_obj_id, array $a_objective_ids)
397 {
398 global $DIC;
399
400 $ilDB = $DIC['ilDB'];
401
402 // are initital test(s) qualifying?
403 include_once "Modules/Course/classes/Objectives/class.ilLOSettings.php";
404 $lo_set = ilLOSettings::getInstanceByObjId($a_obj_id);
405 $initial_qualifying = $lo_set->isInitialTestQualifying();
406
407 // this method returns LP status codes!
408 include_once "Services/Tracking/classes/class.ilLPStatus.php";
409
410 $res = array();
411
412 $sql = "SELECT lor.objective_id, lor.user_id, lor.status, lor.is_final" .
413 " FROM loc_user_results lor" .
414 " JOIN crs_objectives cobj ON (cobj.objective_id = lor.objective_id)" .
415 " WHERE " . $ilDB->in("lor.objective_id", $a_objective_ids, "", "integer");
416 if (!(bool) $initial_qualifying) {
417 $sql .= " AND lor.type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
418 }
419 $sql .= " AND lor.user_id = " . $ilDB->quote($a_user_id, "integer") .
420 " AND cobj.active = " . $ilDB->quote(1, "integer") .
421 " ORDER BY lor.type"; // qualified must come last!
422 $set = $ilDB->query($sql);
423 while ($row = $ilDB->fetchAssoc($set)) {
424 switch ($row["status"]) {
426 if ((bool) $row["is_final"]) {
428 } else {
429 // #15379
431 }
432 break;
433
436 break;
437
438 default:
439 continue 2;
440 }
441
442 // if both initial and qualified, qualified will overwrite initial
443 $res[$row["objective_id"]] = $status;
444 }
445
446 return $res;
447 }
448
449 public static function getSummarizedObjectiveStatusForLP($a_obj_id, array $a_objective_ids, $a_user_id = null)
450 {
451 global $DIC;
452
453 $ilDB = $DIC['ilDB'];
454
455 $GLOBALS['DIC']->logger()->trac()->debug('Get summorized objective status');
456
457 // change event is NOT parsed here!
458
459 // are initital test(s) qualifying?
460 include_once "Modules/Course/classes/Objectives/class.ilLOSettings.php";
461 $lo_set = ilLOSettings::getInstanceByObjId($a_obj_id);
462 $initial_qualifying = $lo_set->isInitialTestQualifying();
463
464 // this method returns LP status codes!
465 include_once "Services/Tracking/classes/class.ilLPStatus.php";
466
467 $res = $tmp_completed = array();
468
469 $sql = "SELECT lor.objective_id, lor.user_id, lor.status, lor.type, lor.is_final" .
470 " FROM loc_user_results lor" .
471 " JOIN crs_objectives cobj ON (cobj.objective_id = lor.objective_id)" .
472 " WHERE " . $ilDB->in("lor.objective_id", $a_objective_ids, "", "integer") .
473 " AND cobj.active = " . $ilDB->quote(1, "integer");
474 if (!(bool) $initial_qualifying) {
475 $sql .= " AND lor.type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
476 }
477 if ($a_user_id) {
478 $sql .= " AND lor.user_id = " . $ilDB->quote($a_user_id, "integer");
479 }
480 $sql .= " ORDER BY lor.type DESC"; // qualified must come first!
481 $set = $ilDB->query($sql);
482
483 $has_final_result = array();
484 while ($row = $ilDB->fetchAssoc($set)) {
485 if ($row['type'] == self::TYPE_QUALIFIED) {
486 $has_final_result[$row['objective_id']] = $row['user_id'];
487 }
488
489 $user_id = (int) $row["user_id"];
490 $status = (int) $row["status"];
491
492 // initial tests only count if no qualified test
493 if (
494 $row["type"] == self::TYPE_INITIAL &&
495 in_array($row['user_id'], (array) $has_final_result[$row['objective_id']])
496 ) {
497 continue;
498 }
499
500 // user did do something
502
503 switch ($status) {
505 $tmp_completed[$user_id]++;
506 break;
507
509 if ((bool) $row["is_final"]) {
510 // object is failed when at least 1 objective is failed without any tries left
512 }
513 break;
514 }
515 }
516
517 $all_nr = sizeof($a_objective_ids);
518 foreach ($tmp_completed as $user_id => $counter) {
519 // if used as precondition object should be completed ASAP, status can be lost on subsequent tries
520 if ($counter == $all_nr) {
522 }
523 }
524
525 if ($a_user_id) {
526 // might return null!
527 return $res[$a_user_id];
528 } else {
529 return $res;
530 }
531 }
532
533 public static function hasResults($a_container_id, $a_user_id)
534 {
535 global $DIC;
536
537 $ilDB = $DIC['ilDB'];
538
539 $query = 'SELECT objective_id FROM loc_user_results ' .
540 'WHERE course_id = ' . $ilDB->quote($a_container_id, 'integer') . ' ' .
541 'AND user_id = ' . $ilDB->quote($a_user_id, 'integer');
542
543 $res = $ilDB->query($query);
544 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
545 return true;
546 }
547 return false;
548 }
549
557 public static function getCompletionsOfUser($a_user_id, $a_from_ts, $a_to_ts)
558 {
559 global $DIC;
560
561 $ilDB = $DIC['ilDB'];
562
563 $res = array();
564
565 $sql = "SELECT lor.objective_id, lor.user_id, lor.status, lor.is_final, lor.tstamp, lor.course_id, cobj.title" .
566 " FROM loc_user_results lor" .
567 " JOIN crs_objectives cobj ON (cobj.objective_id = lor.objective_id)" .
568 " WHERE lor.user_id = " . $ilDB->quote($a_user_id, "integer") .
569 " AND lor.type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer") .
570 " AND lor.tstamp >= " . $ilDB->quote($a_from_ts, "integer") .
571 " AND lor.tstamp <= " . $ilDB->quote($a_to_ts, "integer") .
572 " AND lor.status = " . $ilDB->quote(self::STATUS_COMPLETED, "integer");
573
574 $set = $ilDB->query($sql);
575 while ($row = $ilDB->fetchAssoc($set)) {
576 $res[$row["objective_id"]] = $row;
577 }
578 return $res;
579 }
580}
if(!defined('PATH_SEPARATOR')) $GLOBALS['_PEAR_default_error_mode']
Definition: PEAR.php:64
An exception for terminatinating execution or to throw for unit testing.
static getInstanceByObjId($a_obj_id)
get singleton instance
getSuggestedObjectiveIds()
Get all objectives where the user failed the initial test.
static getCompletionsOfUser($a_user_id, $a_from_ts, $a_to_ts)
Get completed learning objectives for user and time frame.
static isValidType($a_type)
Is given type valid?
findObjectiveIds($a_type=null, $a_status=null, $a_is_final=null)
Find objective ids by type and/or status.
getFailedObjectiveIds($a_is_final=true)
Get all objectives where the user failed the qualified test.
saveObjectiveResult($a_objective_id, $a_type, $a_status, $a_result_percentage, $a_limit_percentage, $a_tries, $a_is_final)
Save objective result.
static deleteResultsForUser($a_user_id)
Delete all result entries for user.
static getSummarizedObjectiveStatusForLP($a_obj_id, array $a_objective_ids, $a_user_id=null)
static deleteResultsFromLP($a_course_id, array $a_user_ids, $a_remove_initial, $a_remove_qualified, array $a_objective_ids)
Delete all (qualified) result entries for course members.
getCourseResultsForUserPresentation()
Get all results for course and user.
getCompletedObjectiveIds()
Get all objectives where the user completed the qualified test.
static isValidStatus($a_status)
Is given status valid?
__construct($a_course_obj_id, $a_user_id)
Constructor.
static getObjectiveStatusForLP($a_user_id, $a_obj_id, array $a_objective_ids)
static resetFinalByObjective($a_objective_id)
static hasResults($a_container_id, $a_user_id)
static lookupResult($a_course_obj_id, $a_user_id, $a_objective_id, $a_tst_type)
Lookup user result.
getCompletedObjectiveIdsByType($a_type)
All completed objectives by type.
static deleteResultsForCourse($a_course_id)
Delete all result entries for course.
const LP_STATUS_COMPLETED_NUM
const LP_STATUS_IN_PROGRESS_NUM
const LP_STATUS_FAILED_NUM
$query
$type
foreach($_POST as $key=> $value) $res
global $ilDB
$a_type
Definition: workflow.php:92
$DIC
Definition: xapitoken.php:46