ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
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 $ilDB;
42
43 $query = 'SELECT * FROM loc_user_results ' .
44 'WHERE user_id = ' . $ilDB->quote($a_user_id, 'integer') . ' ' .
45 'AND course_id = ' . $ilDB->quote($a_course_obj_id, 'integer') . ' ' .
46 'AND objective_id = ' . $ilDB->quote($a_objective_id, 'integer') . ' ' .
47 'AND type = ' . $ilDB->quote($a_tst_type, 'integer');
48 $res = $ilDB->query($query);
49 $ur = array(
50 'status' => self::STATUS_FAILED,
51 'result_perc' => 0,
52 'limit_perc' => 0,
53 'tries' => 0,
54 'is_final' => 0,
55 'has_result' => false
56 );
57 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
58 $ur['status'] = $row->status;
59 $ur['result_perc'] = $row->result_perc;
60 $ur['limit_perc'] = $row->limit_perc;
61 $ur['tries'] = $row->tries;
62 $ur['is_final'] = $row->is_final;
63 $ur['has_result'] = true;
64 }
65 return $ur;
66 }
67
68 public static function resetFinalByObjective($a_objective_id)
69 {
70 $query = 'UPDATE loc_user_results ' .
71 'SET is_final = ' . $GLOBALS['ilDB']->quote(0, 'integer') . ' ' .
72 'WHERE objective_id = ' . $GLOBALS['ilDB']->quote($a_objective_id, 'integer');
73 $GLOBALS['ilDB']->manipulate($query);
74 }
75
76
83 protected static function isValidType($a_type)
84 {
85 return in_array((int) $a_type, array(self::TYPE_INITIAL, self::TYPE_QUALIFIED));
86 }
87
94 protected static function isValidStatus($a_status)
95 {
96 return in_array((int) $a_status, array(self::STATUS_COMPLETED, self::STATUS_FAILED));
97 }
98
105 public static function deleteResultsForUser($a_user_id)
106 {
107 global $ilDB;
108
109 if (!(int) $a_user_id) {
110 return false;
111 }
112
113 $ilDB->manipulate("DELETE FROM loc_user_results" .
114 " WHERE user_id = " . $ilDB->quote($a_user_id, "integer"));
115 return true;
116 }
117
118
125 public static function deleteResultsForCourse($a_course_id)
126 {
127 global $ilDB;
128
129 if (!(int) $a_course_id) {
130 return false;
131 }
132
133 $ilDB->manipulate("DELETE FROM loc_user_results" .
134 " WHERE course_id = " . $ilDB->quote($a_course_id, "integer"));
135 return true;
136 }
137
142 public function delete()
143 {
144 global $ilDB;
145
146 $query = 'DELETE FROM loc_user_results ' .
147 'WHERE course_id = ' . $ilDB->quote($this->course_obj_id) . ' ' .
148 'AND user_id = ' . $ilDB->quote($this->user_id);
149 $ilDB->manipulate($query);
150 }
151
162 public static function deleteResultsFromLP($a_course_id, array $a_user_ids, $a_remove_initial, $a_remove_qualified, array $a_objective_ids)
163 {
164 global $ilDB;
165
166 if (!(int) $a_course_id ||
167 !sizeof($a_user_ids)) {
168 return false;
169 }
170
171 $base_sql = "DELETE FROM loc_user_results" .
172 " WHERE course_id = " . $ilDB->quote($a_course_id, "integer") .
173 " AND " . $ilDB->in("user_id", $a_user_ids, "", "integer");
174
175 if ((bool) $a_remove_initial) {
176 $sql = $base_sql .
177 " AND type = " . $ilDB->quote(self::TYPE_INITIAL, "integer");
178 $ilDB->manipulate($sql);
179 }
180
181 if ((bool) $a_remove_qualified) {
182 $sql = $base_sql .
183 " AND type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
184 $ilDB->manipulate($sql);
185 }
186
187 if (is_array($a_objective_ids)) {
188 $sql = $base_sql .
189 " AND " . $ilDB->in("objective_id", $a_objective_ids, "", "integer");
190 $ilDB->manipulate($sql);
191 }
192
193 $ilDB->manipulate($sql);
194 return true;
195 }
196
197
210 public function saveObjectiveResult($a_objective_id, $a_type, $a_status, $a_result_percentage, $a_limit_percentage, $a_tries, $a_is_final)
211 {
212 global $ilDB;
213
214 if (!self::isValidType($a_type) ||
215 !self::isValidStatus($a_status)) {
216 return false;
217 }
218 $ilDB->replace(
219 "loc_user_results",
220 array(
221 "course_id" => array("integer", $this->course_obj_id),
222 "user_id" => array("integer", $this->user_id),
223 "objective_id" => array("integer", $a_objective_id),
224 "type" => array("integer", $a_type)
225 ),
226 array(
227 "status" => array("integer", $a_status),
228 "result_perc" => array("integer", $a_result_percentage),
229 "limit_perc" => array("integer", $a_limit_percentage),
230 "tries" => array("integer", $a_tries),
231 "is_final" => array("integer", $a_is_final),
232 "tstamp" => array("integer", time()),
233 )
234 );
235 return true;
236 }
237
246 protected function findObjectiveIds($a_type = null, $a_status = null, $a_is_final = null)
247 {
248 global $ilDB;
249
250 $res = array();
251
252 $sql = "SELECT objective_id" .
253 " FROM loc_user_results" .
254 " WHERE course_id = " . $ilDB->quote($this->course_obj_id, "integer") .
255 " AND user_id = " . $ilDB->quote($this->user_id, "integer");
256
257 if ($this->isValidType($a_type)) {
258 $sql .= " AND type = " . $ilDB->quote($a_type, "integer");
259 }
260 if ($this->isValidStatus($a_status)) {
261 $sql .= " AND status = " . $ilDB->quote($a_status, "integer");
262 }
263 if ($a_is_final !== null) {
264 $sql .= " AND is_final = " . $ilDB->quote($a_is_final, "integer");
265 }
266
267 $set = $ilDB->query($sql);
268 while ($row = $ilDB->fetchAssoc($set)) {
269 $res[] = $row["objective_id"];
270 }
271
272 return $res;
273 }
274
281 {
282 return $this->findObjectiveIds($a_type, self::STATUS_COMPLETED);
283 }
284
290 public function getSuggestedObjectiveIds()
291 {
292 return $this->findObjectiveIds(self::TYPE_INITIAL, self::STATUS_FAILED);
293 }
294
300 public function getCompletedObjectiveIds()
301 {
302 include_once './Modules/Course/classes/Objectives/class.ilLOSettings.php';
303 $settings = ilLOSettings::getInstanceByObjId($this->course_obj_id);
304
305 if (!$settings->isInitialTestQualifying() or !$settings->worksWithInitialTest()) {
306 return $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_COMPLETED);
307 }
308
309 // status of final final test overwrites initial qualified.
310 if (
311 $settings->isInitialTestQualifying() &&
312 $settings->worksWithInitialTest()
313 ) {
314 $completed = array();
315 $completed_candidates = array_unique(
316 array_merge(
317 $this->findObjectiveIds(self::TYPE_INITIAL, self::STATUS_COMPLETED),
318 $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_COMPLETED)
319 )
320 );
321 $failed_final = (array) $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_FAILED);
322
323 foreach ($completed_candidates as $objective_completed) {
324 if (!in_array($objective_completed, $failed_final)) {
325 $completed[] = $objective_completed;
326 }
327 }
328 return $completed;
329 }
330 }
331
338 public function getFailedObjectiveIds($a_is_final = true)
339 {
340 return $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_FAILED, $a_is_final);
341 }
342
349 {
350 global $ilDB;
351
352 $res = array();
353
354 include_once("./Modules/Course/classes/Objectives/class.ilLOSettings.php");
355 $settings = ilLOSettings::getInstanceByObjId($this->course_obj_id);
356
357 $set = $ilDB->query("SELECT *" .
358 " FROM loc_user_results" .
359 " WHERE course_id = " . $ilDB->quote($this->course_obj_id, "integer") .
360 " AND user_id = " . $ilDB->quote($this->user_id, "integer"));
361 while ($row = $ilDB->fetchAssoc($set)) {
362 // do not read initial test results, if disabled.
363 if (
364 $row['type'] == self::TYPE_INITIAL &&
365 !$settings->worksWithInitialTest()
366 ) {
367 continue;
368 }
369
370 $objective_id = $row["objective_id"];
371 $type = $row["type"];
372 unset($row["objective_id"]);
373 unset($row["type"]);
374 $res[$objective_id][$type] = $row;
375 }
376
377 return $res;
378 }
379
380 public static function getObjectiveStatusForLP($a_user_id, $a_obj_id, array $a_objective_ids)
381 {
382 global $ilDB;
383
384 // are initital test(s) qualifying?
385 include_once "Modules/Course/classes/Objectives/class.ilLOSettings.php";
386 $lo_set = ilLOSettings::getInstanceByObjId($a_obj_id);
387 $initial_qualifying = $lo_set->isInitialTestQualifying();
388
389 // this method returns LP status codes!
390 include_once "Services/Tracking/classes/class.ilLPStatus.php";
391
392 $res = array();
393
394 $sql = "SELECT lor.objective_id, lor.user_id, lor.status, lor.is_final" .
395 " FROM loc_user_results lor" .
396 " JOIN crs_objectives cobj ON (cobj.objective_id = lor.objective_id)" .
397 " WHERE " . $ilDB->in("lor.objective_id", $a_objective_ids, "", "integer");
398 if (!(bool) $initial_qualifying) {
399 $sql .= " AND lor.type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
400 }
401 $sql .= " AND lor.user_id = " . $ilDB->quote($a_user_id, "integer") .
402 " AND cobj.active = " . $ilDB->quote(1, "integer") .
403 " ORDER BY lor.type"; // qualified must come last!
404 $set = $ilDB->query($sql);
405 while ($row = $ilDB->fetchAssoc($set)) {
406 switch ($row["status"]) {
408 if ((bool) $row["is_final"]) {
410 } else {
411 // #15379
413 }
414 break;
415
418 break;
419
420 default:
421 /*
422 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
423 break;
424 */
425 continue;
426 }
427
428 // if both initial and qualified, qualified will overwrite initial
429 $res[$row["objective_id"]] = $status;
430 }
431
432 return $res;
433 }
434
435 public static function getSummarizedObjectiveStatusForLP($a_obj_id, array $a_objective_ids, $a_user_id = null)
436 {
437 global $ilDB;
438
439 $GLOBALS['DIC']->logger()->trac()->debug('Get summorized objective status');
440
441 // change event is NOT parsed here!
442
443 // are initital test(s) qualifying?
444 include_once "Modules/Course/classes/Objectives/class.ilLOSettings.php";
445 $lo_set = ilLOSettings::getInstanceByObjId($a_obj_id);
446 $initial_qualifying = $lo_set->isInitialTestQualifying();
447
448 // this method returns LP status codes!
449 include_once "Services/Tracking/classes/class.ilLPStatus.php";
450
451 $res = $tmp_completed = array();
452
453 $sql = "SELECT lor.objective_id, lor.user_id, lor.status, lor.type, lor.is_final" .
454 " FROM loc_user_results lor" .
455 " JOIN crs_objectives cobj ON (cobj.objective_id = lor.objective_id)" .
456 " WHERE " . $ilDB->in("lor.objective_id", $a_objective_ids, "", "integer") .
457 " AND cobj.active = " . $ilDB->quote(1, "integer");
458 if (!(bool) $initial_qualifying) {
459 $sql .= " AND lor.type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
460 }
461 if ($a_user_id) {
462 $sql .= " AND lor.user_id = " . $ilDB->quote($a_user_id, "integer");
463 }
464 $sql .= " ORDER BY lor.type DESC"; // qualified must come first!
465 $set = $ilDB->query($sql);
466
467 $has_final_result = array();
468 while ($row = $ilDB->fetchAssoc($set)) {
469 if ($row['type'] == self::TYPE_QUALIFIED) {
470 $has_final_result[$row['objective_id']] = $row['user_id'];
471 }
472
473 $user_id = (int) $row["user_id"];
474 $status = (int) $row["status"];
475
476 // initial tests only count if no qualified test
477 if (
478 $row["type"] == self::TYPE_INITIAL &&
479 in_array($row['user_id'], (array) $has_final_result[$row['objective_id']])
480 ) {
481 continue;
482 }
483
484 // user did do something
486
487 switch ($status) {
489 $tmp_completed[$user_id]++;
490 break;
491
493 if ((bool) $row["is_final"]) {
494 // object is failed when at least 1 objective is failed without any tries left
496 }
497 break;
498 }
499 }
500
501 $all_nr = sizeof($a_objective_ids);
502 foreach ($tmp_completed as $user_id => $counter) {
503 // if used as precondition object should be completed ASAP, status can be lost on subsequent tries
504 if ($counter == $all_nr) {
506 }
507 }
508
509 if ($a_user_id) {
510 // might return null!
511 return $res[$a_user_id];
512 } else {
513 return $res;
514 }
515 }
516
517 public static function hasResults($a_container_id, $a_user_id)
518 {
519 global $ilDB;
520
521 $query = 'SELECT objective_id FROM loc_user_results ' .
522 'WHERE course_id = ' . $ilDB->quote($a_container_id, 'integer') . ' ' .
523 'AND user_id = ' . $ilDB->quote($a_user_id, 'integer');
524
525 $res = $ilDB->query($query);
526 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
527 return true;
528 }
529 return false;
530 }
531}
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 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
$counter
$GLOBALS['loaded']
Global hash that tracks already loaded includes.
$query
$type
foreach($_POST as $key=> $value) $res
global $ilDB
$a_type
Definition: workflow.php:92